package com.ideal.util.file;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.util.StringUtil;
import com.ideal.dto.config.res.execl.ExeclData;
import com.ideal.dto.config.res.execl.TableColumns;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author songfz
 * @Description
 * @Date 2022/7/19
 */
public class ExeclUtil {

    private static Logger LOG = Logger.getLogger(ExeclUtil.class);

    /**
     * 读取execl文件内容
     * @param path
     * @return
     */
    public static String readContext(String path) {
        List<Map<String, List<List<String>>>> context = new ArrayList<>();
        try (FileInputStream is = new FileInputStream(path)) {

            HSSFWorkbook ws = new HSSFWorkbook(is);

            for (int m = 0; m < ws.getNumberOfSheets(); ++m) {
                Map<String, List<List<String>>> sheetMap = new HashMap<>(64);
                HSSFSheet sheet = ws.getSheetAt(m);
                String sheetName = sheet.getSheetName();
                //获取每行数据
                final List<List<String>> list = new ArrayList<>();
                //String[][] rows = new String[sheet.getLastRowNum()][sheet.getRow(0).getLastCellNum()];
                for (int i = 0; i <= sheet.getLastRowNum(); ++i) {
                    HSSFRow row = sheet.getRow(i);
                    List<String> rowList = new ArrayList<>();
                    rowList.add(String.valueOf(row.getRowNum()));
                    for (int j = 0; j < row.getLastCellNum(); ++j) {
                        HSSFCell cell = row.getCell(j);

                        String cellValue = getCellValue(cell);
                        //rows[i][j] = cellValue;
                        rowList.add(cellValue);
                    }
                    list.add(rowList);
                    sheetMap.put(sheetName, list);
                }
                context.add(sheetMap);
            }
        } catch (Exception e) {
            LOG.error(e.getMessage(), e);
        }
        return JSONObject.toJSONString(context);
    }

    private static String getCellValue(HSSFCell cell) {
        String value = "";
        if (Objects.isNull(cell)) {
            return value;
        }
        int cellType = cell.getCellType();

        if (cellType == HSSFCell.CELL_TYPE_STRING) {
            value = cell.getStringCellValue();

        } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
            double numericCellValue = cell.getNumericCellValue();
            Date date = HSSFDateUtil.getJavaDate(numericCellValue);
            value = new SimpleDateFormat("").format(date);
        } else if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
            double cellValue = cell.getNumericCellValue();
            value = String.valueOf(cellValue);
            if (isIntegerNum(value)) {
                value = String.valueOf((int) cellValue);
            }
        } else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
            value = String.valueOf(cell.getBooleanCellValue());
        } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
            value = "";
        } else {
            value = cell.getStringCellValue();
        }
        if (StringUtil.isEmpty(value)) {
            value = "";
        }
        value = value.trim();
        return value;
    }

    private static boolean isIntegerNum(String value) {
        String[] strNum = value.split("\\.");
        try {
            if (Integer.parseInt(strNum[1]) == 0) {
                return true;
            }
        } catch (ArrayIndexOutOfBoundsException ex) {
            return true;
        }
        return false;
    }


    /**
     * JSON字符串转集合
     * @param content
     * @return
     */
    public static  List<Map<String, List<List<String>>>> strToJson(String content){
        //List<Map<String, String[][]>> contents = new ArrayList<>();
       return  JSON.parseObject( content,List.class);
    }

    /**
     * 转换execl数据格式（页面回显execl 数据使用）
     * @param lists
     * @return Map<表头集合，数据集合>
     */
    public static ExeclData toConvert(List<Map<String, List<List<String>>>> lists){
        final ExeclData execlData = new ExeclData();
        final List<List<TableColumns>> headerList = new ArrayList<>();
        List<List<Map<Integer, String>>> contentList = new ArrayList<>();
        final List<String> sheetNames = new ArrayList<>();
        boolean ss = true;
        //每个sheet
        for (Map<String, List<List<String>>> map : lists) {

            final List<TableColumns> columnsList = new ArrayList<>();
            List<Map<Integer, String>> contents = new ArrayList<>();
            // sheet 名与内容键值对
            for (String key : map.keySet()) {

                sheetNames.add(key);

                final List<List<String>> value = map.get(key);

                //sheet内内容
                for (int j=1;j<value.size();++j) {
                    Map<Integer, String> contentMap = new HashMap<>();
                    for (int i=0;i<value.get(j).size(); ++i){

                        if (ss) {
                            final TableColumns tableColumns = new TableColumns();
                            tableColumns.setProp(String.valueOf(i));
                            tableColumns.setLabel(value.get(0).get(i));
                            columnsList.add(tableColumns);
                            System.out.println(i);
                        }

                        contentMap.put(i,value.get(j).get(i).trim());
                    }
                    if(ss){

                        headerList.add(columnsList);
                    }
                    ss = false;
                    contents.add(contentMap);

                }

                ss = true;
            }
            contentList.add(contents);


        }
        execlData.setSheetNames(sheetNames);
        execlData.setTableColumns(headerList);
        execlData.setExeclContent(contentList);
        System.out.println("内容："+contentList.size()+"-"+contentList);
        System.out.println("表头:"+headerList.size()+"-"+headerList);
        System.out.println("sheetName:"+sheetNames.size()+"-"+sheetNames);
        return execlData;
    }
    public static void main(String[] args) {
        String url = "F:\\pdf\\测试数据.xls";

        final String content = readContext(url);
        System.out.println("execl文件内容："+content);
        final List<Map<String, List<List<String>>>> maps = strToJson(content);
        toConvert(maps);
        //System.out.println(maps);
    }
}
